%macro get_f941;

	proc printto new log = "&logdir./get_brtf_f941.txt";
	run;

	proc sql;
		[redacted]
		select * from connection to iq (
		
		select
			a.[redacted] as firm_id,
			a.[redacted] as tp,
			a.[redacted] as zip,
			max(a.[redacted]) as num_emp,
			max(coalesce(a.[redacted],0) + 
				coalesce(a.[redacted],0)) as cobra,
			max(coalesce(a.[redacted],0) +
				coalesce(a.[redacted],0) +
				coalesce(a.[redacted],0) + 
				coalesce(a.[redacted],0)) as ffcra,
			max(coalesce(a.[redacted],0) +
				coalesce(a.[redacted],0)) as ertc,
			max(a.[redacted]) as incm_tax,
			max(a.[redacted]) as wg_amt,
			max(a.[redacted]) as ss_med_tax,
			max(a.[redacted]) as deferred,
			max(a.[redacted]) as f7200,
			max(a.[redacted]) as balance_due,
			max(a.[redacted]) as deposits,
			max(c.[redacted]) as prepein,
			max(c.[redacted]) as preptin
			from [redacted] as a
				left outer join [redacted] as c
					on(a.[redacted] = c.[redacted] and
						a.[redacted] = c.[redacted] and
						a.[redacted] = c.[redacted] and
						a.[redacted] = c.[redacted] and
						a.[redacted] = c.[redacted] and
						a.[redacted] = c.[redacted] and
						a.[redacted] = c.[redacted] and
						a.[redacted] = c.[redacted]
					)
			where a.[redacted] between 202006 and 202112
			group by a.[redacted],
				a.[redacted],
				a.[redacted]
			order by firm_id, tp
		
		);
	disconnect from iq;
	quit;
	
	/* drop duplicates -- this is almost certainly overkill */
	data f941;
		set f941;
		valid_zip = zip ~= "00000" & zip~="";
	run;	
		
	proc means data = f941 nway noprint;
		class firm_id tp;
		output out = num_obs(drop=_type_ _freq_)
			min(valid_zip) = min_valid
			max(valid_zip) = max_valid;
	run;
	
	data f941;
		merge f941 num_obs(where=(min_valid = 0 & max_valid = 1) in=b);
		by firm_id tp;
		if b = 1 & zip = "00000" then delete;
		drop valid_zip min_valid max_valid;
	run;
	
	/* drop the rest arbitrarily */
	proc sort data=  f941 nodupkey
		out = rlib.f941;
		by firm_id tp;
	run;
		
	
	
	
	
	proc printto;
	run;
	
	
%mend;
